﻿using Microsoft.EntityFrameworkCore;
using NanjingbeiRailwayStation.Data.ClickHouse.Models;

namespace NanjingbeiRailwayStation.Data.ClickHouse.Services
{
    /// <summary>
    /// 
    /// </summary>
    public class DeviceEntityService
    {

       /// <summary>
       /// 获取设备总数量
       /// </summary>
       /// <returns></returns>
        public async Task<int> GetTotalCount()
        {
            using var ctx = new Db();
            return await ctx.DeviceEntitie.CountAsync();
        }

        /// <summary>
        /// 获取塔吊类设备总数量
        /// </summary>
        /// <returns></returns>
        public async Task<ulong> GetTowerCraneTotalCount()
        {
            return await GetTotalCountByDeviceClass(Core.StringConstant.DeviceClass_TowerCrane);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public async Task<ulong> GetTowerCraneNormalCount()
        {
            return await GetTotalCountByDeviceClass(Core.StringConstant.DeviceClass_TowerCrane);

        }

        /// <summary>
        /// 获取今日有报警的塔吊设备数量 
        /// </summary>
        /// <returns></returns>
        public async Task<ulong> GetTodayCountOfTowerCraneEquipmentWithAlarms()
        {
          
            using var db = new NanjingbeiRailwayStation.Data.ClickHouse.Db();

            var sql = @"select count(*) as Count from (
                        select imei, count( DISTINCT (imei) ) as cnt
                        from jindujiangong.EventReport
                        where  endsWith(eventName,'Alarm')
                        and eventTime>(date_add(now(), INTERVAL 0 HOUR)-INTERVAL 10 minute)
                        group by imei
                        ) as ta
                        join jindujiangong.DeviceEntitie as tb
                        on ta.imei=tb.imei
                        where tb.DeviceClass='TowerCrane'";

            var entity = await db.Database.SqlQueryRaw<TodayCount>(sql).FirstOrDefaultAsync();

            return entity == null ? 0 : entity.Count;

        }
        /// <summary>
        /// 获取今日正常的的塔吊设备数量
        /// </summary>
        /// <returns></returns>

        public async Task<ulong> GetTodayCountOfTowerCraneEquipmentWithNormal()
        {

            using var db = new NanjingbeiRailwayStation.Data.ClickHouse.Db();

            var sql = @"    select 
                       count(  DISTINCT( imei)) Count from 
                       jindujiangong.RealTimeElectricalParameters_Latest   as tsa final
                        join jindujiangong.DeviceEntitie as tsb
                        on tsa.imei =tsb.imei
                        where  eventTime>(date_sub(now(), INTERVAL 60 minute)) 
                        and tsb.DeviceClass='TowerCrane'
                        and imei  not in (select imei as Count from (
				                        select imei, count( DISTINCT (imei) ) as cnt
				                        from jindujiangong.EventReport
				                        where  endsWith(eventName,'Alarm')
				                        and eventTime>(date_add(now(), INTERVAL 0 HOUR)-INTERVAL 10 minute)
				                        group by imei
				                        ) as ta
				                        join jindujiangong.DeviceEntitie as tb
				                        on ta.imei=tb.imei
				                        where tb.DeviceClass='TowerCrane')";

            var entity = await db.Database.SqlQueryRaw<TodayCount>(sql).FirstOrDefaultAsync();

            return entity == null ? 0 : entity.Count;

        }



        /// <summary>
        /// 获取设备总数量
        /// </summary>
        /// <returns></returns>
        private async Task<ulong> GetTotalCountByDeviceClass(string deviceClass)
        {
            using var db = new NanjingbeiRailwayStation.Data.ClickHouse.Db();

            var sql = string.Format(@"select 
                       count(  DISTINCT( imei)) Count from 
                        jindujiangong.DeviceEntitie 
                        where DeviceClass='{0}' ", deviceClass);

            var entity = await db.Database.SqlQueryRaw<TodayCount>(sql).FirstOrDefaultAsync();

            return entity == null ? 0 : entity.Count;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="deviceClass"></param>
        /// <returns></returns>
        public async Task<List<Entities.DeviceEntity>> List(string deviceClass)
        {
            using var db = new NanjingbeiRailwayStation.Data.ClickHouse.Db();

            var sql = string.Format(@"SELECT imei, DeviceCode, DeviceName, DeviceMode, DeviceClass, InstallationLocation, Longitude, Latitude, InstallationPosition, Director, DirectorTelphone, Installer, InstallerTelphone
FROM jindujiangong.DeviceEntitie;", deviceClass);

            var entitys = await db.Database.SqlQueryRaw<Entities.DeviceEntity>(sql).ToListAsync();
            return entitys;
        }



    }
}
